package com.xhc.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.ArrayList;
import java.util.List;

public class ExcelProcessor {
    private static final int QR_COLUMN_WIDTH = 30 * 246; // 30个字符宽度
    private static final int QR_ROW_HEIGHT = 200 * 20; // 增加行高以容纳二维码和额外文本
    private static final int QR_COLS_PER_ROW = 3; // 每行3个二维码

    public static void processExcelToQR(String inputPath, String outputPath,
                                        String sheetName, String columnA, String columnB,
                                        String columnC, String columnD, String columnE) throws Exception {
        try (FileInputStream fis = new FileInputStream(inputPath);
             Workbook inputWorkbook = WorkbookFactory.create(fis)) {

            // 验证工作表是否存在
            Sheet inputSheet = null;
            for (int i = 0; i < inputWorkbook.getNumberOfSheets(); i++) {
                if (inputWorkbook.getSheetAt(i).getSheetName().equalsIgnoreCase(sheetName)) {
                    inputSheet = inputWorkbook.getSheetAt(i);
                    break;
                }
            }

            if (inputSheet == null) {
                throw new Exception("工作表 '" + sheetName + "' 不存在");
            }

            // 验证列是否有效
            int colAIndex = columnToIndex(columnA);
            int colBIndex = columnToIndex(columnB);
            int colCIndex = columnToIndex(columnC);
            int colDIndex = columnToIndex(columnD);
            int colEIndex = columnToIndex(columnE);

            if (colAIndex < 0 || colBIndex < 0 || colCIndex < 0 || colDIndex < 0 || colEIndex < 0) {
                throw new Exception("无效的列");
            }

            // 收集数据
            List<CellData> dataList = collectData(inputSheet, colAIndex, colBIndex, colCIndex, colDIndex, colEIndex, 1);

            // 创建新工作簿并处理
            createOutputWorkbook(inputWorkbook, dataList, outputPath, sheetName);
        }
    }

    private static int columnToIndex(String column) {
        column = column.toUpperCase();
        if (column.length() == 1) {
            return column.charAt(0) - 'A';
        } else if (column.length() == 2) {
            return (column.charAt(0) - 'A' + 1) * 26 + (column.charAt(1) - 'A');
        }
        return -1;
    }

    private static List<CellData> collectData(Sheet sheet, int colAIndex, int colBIndex,
                                              int colCIndex, int colDIndex, int colEIndex, int startRow) {
        List<CellData> dataList = new ArrayList<>();

        int lastRowNum = sheet.getLastRowNum();

        for (int i = startRow ; i < lastRowNum + 1; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                Cell cellA = row.getCell(colAIndex);
                Cell cellB = row.getCell(colBIndex);
                Cell cellC = row.getCell(colCIndex);
                Cell cellD = row.getCell(colDIndex);
                Cell cellE = row.getCell(colEIndex);

                String valueA = getCellValueAsString(cellA);
                String valueB = convertToDateString(cellB);
                String valueC = getCellValueAsString(cellC);
                String valueD = getCellValueAsString(cellD);
                String valueE = getCellValueAsString(cellE);

                if (!valueA.isEmpty()) {
                    dataList.add(new CellData(valueA, valueB, valueC, valueD, valueE));
                }
            }
        }
        return dataList;
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                // 对于A列，直接转换为字符串
                double num = cell.getNumericCellValue();
                if (num == (long) num) {
                    return String.valueOf((long) num);
                } else {
                    return String.valueOf(num);
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                // 对于公式单元格，直接获取计算结果的字符串表示
                try {
                    switch (cell.getCachedFormulaResultType()) {
                        case STRING:
                            return cell.getStringCellValue().trim();
                        case NUMERIC:
                            double formulaNum = cell.getNumericCellValue();
                            if (formulaNum == (long) formulaNum) {
                                return String.valueOf((long) formulaNum);
                            } else {
                                return String.valueOf(formulaNum);
                            }
                        case BOOLEAN:
                            return String.valueOf(cell.getBooleanCellValue());
                        default:
                            return cell.getCellFormula();
                    }
                } catch (Exception e) {
                    return cell.getCellFormula();
                }
            default:
                return "";
        }
    }

    private static String convertToDateString(Cell cell) {
        if (cell == null) {
            return "";
        }

        try {
            // 首先检查是否是日期格式的单元格
            if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
                // 如果是日期格式，直接格式化为日期字符串
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/M/d");
                return sdf.format(cell.getDateCellValue());
            }

            // 如果不是日期格式，检查是否是数值（Excel日期序列号）
            if (cell.getCellType() == CellType.NUMERIC) {
                double numericValue = cell.getNumericCellValue();

                // Excel日期序列号通常大于20000（大约对应1954年之后）
                if (numericValue > 20000) {
                    // 将Excel日期序列号转换为Java Date对象
                    Date javaDate = DateUtil.getJavaDate(numericValue);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/M/d");
                    return sdf.format(javaDate);
                } else {
                    // 如果不是日期序列号，直接返回数值
                    if (numericValue == (long) numericValue) {
                        return String.valueOf((long) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            }

            // 对于其他类型的单元格，使用常规方法获取字符串值
            return getCellValueAsString(cell);

        } catch (Exception e) {
            // 如果转换失败，返回原始字符串值
            return getCellValueAsString(cell);
        }
    }

    private static void createOutputWorkbook(Workbook inputWorkbook, List<CellData> dataList,
                                             String outputPath, String sheetName) throws Exception {
        try (Workbook outputWorkbook = new XSSFWorkbook()) {
            // 复制原始工作表（保持格式）
            copySheetsWithFormat(inputWorkbook, outputWorkbook);

            // 添加二维码工作表
            addQRSheet(outputWorkbook, dataList, sheetName);

            // 保存文件
            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                outputWorkbook.write(fos);
            }
        }
    }

    private static void copySheetsWithFormat(Workbook srcWorkbook, Workbook destWorkbook) {
        for (int i = 0; i < srcWorkbook.getNumberOfSheets(); i++) {
            Sheet srcSheet = srcWorkbook.getSheetAt(i);
            Sheet destSheet = destWorkbook.createSheet(srcSheet.getSheetName());
            copySheetWithFormat(srcSheet, destSheet);
        }
    }

    private static void copySheetWithFormat(Sheet srcSheet, Sheet destSheet) {
        for (int i = 0; i <= srcSheet.getLastRowNum(); i++) {
            Row srcRow = srcSheet.getRow(i);
            if (srcRow != null) {
                Row destRow = destSheet.createRow(i);
                copyRowWithFormat(srcRow, destRow);
            }
        }
    }

    private static void copyRowWithFormat(Row srcRow, Row destRow) {
        for (int j = 0; j < srcRow.getLastCellNum(); j++) {
            Cell srcCell = srcRow.getCell(j);
            if (srcCell != null) {
                Cell destCell = destRow.createCell(j);
                copyCellWithFormat(srcCell, destCell);
            }
        }
    }

    private static void copyCellWithFormat(Cell srcCell, Cell destCell) {
        // 复制单元格样式
        CellStyle newStyle = destCell.getSheet().getWorkbook().createCellStyle();
        newStyle.cloneStyleFrom(srcCell.getCellStyle());
        destCell.setCellStyle(newStyle);

        // 复制单元格值
        switch (srcCell.getCellType()) {
            case STRING:
                destCell.setCellValue(srcCell.getStringCellValue());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    destCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    destCell.setCellValue(srcCell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                destCell.setCellValue(srcCell.getBooleanCellValue());
                break;
            case FORMULA:
                destCell.setCellFormula(srcCell.getCellFormula());
                break;
            default:
                break;
        }
    }

    private static void addQRSheet(Workbook workbook, List<CellData> dataList, String sheetName) {
        Sheet qrSheet = workbook.createSheet(sheetName + "_含二维码");

        // 设置列宽
        for (int i = 0; i < QR_COLS_PER_ROW; i++) {
            qrSheet.setColumnWidth(i, QR_COLUMN_WIDTH);
        }

        // 计算需要多少行
        int totalRows = (int) Math.ceil((double) dataList.size() / QR_COLS_PER_ROW);

        // 填充数据
        for (int i = 0; i < dataList.size(); i++) {
            int rowIndex = i / QR_COLS_PER_ROW;
            int colIndex = i % QR_COLS_PER_ROW;

            // 创建行（如果不存在）
            Row row = qrSheet.getRow(rowIndex);
            if (row == null) {
                row = qrSheet.createRow(rowIndex);
                row.setHeightInPoints(QR_ROW_HEIGHT / 20f);
            }

            CellData data = dataList.get(i);

            // 生成显示文本
            String displayText = data.valueA + " / " + data.valueB;
            // 将C列和D列数据用"-"拼接作为箱号段
            String boxRange = data.valueC + "-" + data.valueD;
            // E列数据作为箱型
            String boxType = data.valueE;

            // 使用A列数据(data.valueA)作为二维码内容
            addQRImage(workbook, qrSheet, data.valueA, displayText, boxRange, boxType, rowIndex, colIndex);
        }
    }

    private static void addQRImage(Workbook workbook, Sheet sheet, String qrContent,
                                   String displayText, String boxRange, String boxType, int rowNum, int colNum) {
        try {
            byte[] qrImage = QRCodeGenerator.generateQRCodeImage(qrContent, displayText, boxRange, boxType);
            int pictureIdx = workbook.addPicture(qrImage, Workbook.PICTURE_TYPE_PNG);

            Drawing<?> drawing = sheet.createDrawingPatriarch();
            CreationHelper helper = workbook.getCreationHelper();

            ClientAnchor anchor = helper.createClientAnchor();
            anchor.setCol1(colNum);
            anchor.setRow1(rowNum);
            anchor.setCol2(colNum + 1);
            anchor.setRow2(rowNum + 1);

            drawing.createPicture(anchor, pictureIdx);
        } catch (Exception e) {
            System.err.println("生成二维码失败: " + e.getMessage());
        }
    }

    // 内部类用于存储A列、B列、C列、D列和E列数据
    private static class CellData {
        String valueA;
        String valueB;
        String valueC;
        String valueD;
        String valueE;

        CellData(String valueA, String valueB, String valueC, String valueD, String valueE) {
            this.valueA = valueA;
            this.valueB = valueB;
            this.valueC = valueC;
            this.valueD = valueD;
            this.valueE = valueE;
        }
    }
}